package com.livelab.java;

import com.livelab.model.ExerciseProgress;
import com.livelab.util.MySQLDatabaseUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;

/**
 *
 * @author Orangeagle- Jason Ambos
 */
@ManagedBean
@ViewScoped
public class ExerciseProgressBean {
    
    private static final String UPDATE = "UPDATE agslog SET score = ? WHERE exerciseName = ? AND username = ?;";
    private final String[] dateRanges = {"Due Date After Current Time", "Due Date Within or After The Past Week", 
        "Due Date Within or After The Past Two Weeks", "Due Date Within or After The Past Three Weeks", 
        "Due Date Within or After The Past Month", "Due Date Within or After The Past Two Months", 
        "Due Date Within or After The Past Three Months", "Due Date Within or After The Past Four Months", 
        "Due Date Within or After The Past Five Months", "Due Date Within or After The Past Six Months", "All"};
    
    private String selectedDateRange;
    private ArrayList<ExerciseProgress> exerciseProgressTableList;
    private MySQLDatabaseUtil db;
    
    @ManagedProperty(value="#{instructorLoginBean}")
    private InstructorLoginBean loginBean;
    
    public ExerciseProgressBean() {
        db = new MySQLDatabaseUtil("localhost/javalivelab9e", "agsuser", "agsuser");
        
        exerciseProgressTableList = new ArrayList<>();      
    }
    
    // Selects the date interval to search through database
    private String sqlDateRange(String selectedDateRange) {
        if (selectedDateRange.equals(dateRanges[0])) {
            return " AND whenUpdated > NOW()";
        } 
        else if (selectedDateRange.equals(dateRanges[1])) {
            return " AND whenUpdated > NOW() - INTERVAL 1 WEEK";
        }
        else if (selectedDateRange.equals(dateRanges[2])) {
            return " AND whenUpdated > NOW() - INTERVAL 2 WEEK";
        }
        else if (selectedDateRange.equals(dateRanges[3])) {
            return " AND whenUpdated > NOW() - INTERVAL 3 WEEK";
        }
        else if (selectedDateRange.equals(dateRanges[4])) {
            return " AND whenUpdated > NOW() - INTERVAL 1 MONTH";
        }
        else if (selectedDateRange.equals(dateRanges[5])) {
            return " AND whenUpdated > NOW() - INTERVAL 2 MONTH";
        }
        else if (selectedDateRange.equals(dateRanges[6])) {
            return " AND whenUpdated > NOW() - INTERVAL 3 MONTH";
        }
        else if (selectedDateRange.equals(dateRanges[7])) {
            return " AND whenUpdated > NOW() - INTERVAL 4 MONTH";
        }
        else if (selectedDateRange.equals(dateRanges[8])) {
            return " AND whenUpdated > NOW() - INTERVAL 5 MONTH";
        }
        else if (selectedDateRange.equals(dateRanges[9])) {
            return " AND whenUpdated > NOW() - INTERVAL 6 MONTH";
        }
        else {
            return "";
        }
    }

    /*** Getters ***/
    public String[] getDateRanges() {
        return dateRanges;
    }

    public String getSelectedDateRange() {
        return selectedDateRange;
    }
    
    public ArrayList<ExerciseProgress> getExerciseProgressTableList() {
        return exerciseProgressTableList;
    }

    /*** Setters ***/
    public void setSelectedDateRange(String selectedDateRange) {
        this.selectedDateRange = selectedDateRange;
    }

    public void setExerciseProgressTableList(ArrayList<ExerciseProgress> exerciseProgressTableList) {
        this.exerciseProgressTableList = exerciseProgressTableList;
    }
    
    /*** Button Action Methods ***/
    public void refreshDateRange() {
        FacesContext context = FacesContext.getCurrentInstance();
        exerciseProgressTableList.clear();
        double maxScore = 0;
        String username;
        String exerciseName;
        ResultSet temp;
        ResultSet rs;
        
        rs = db.executeQuery("SELECT agsstudent.lastname, agsstudent.firstname, agsstudent.username, agslog.exerciseName, agslog.score FROM agsstudent, agslog WHERE agsstudent.courseID = '" + loginBean.getCourseId() + "'" + sqlDateRange(selectedDateRange) + ";");
        try {
            while (rs.next()) {
                username = rs.getString(3);
                exerciseName = rs.getString(4);
                
                temp = db.executeQuery("SELECT exerciseassigned.score FROM exerciseassigned, agsinstructor WHERE agsinstructor.courseid = '" + loginBean.getCourseId() + "' AND exerciseassigned.exerciseName = '" + exerciseName + "';");
                while (temp.next()) {
                    maxScore = temp.getDouble(1);
                }
                exerciseProgressTableList.add(new ExerciseProgress(rs.getString(1), rs.getString(2), exerciseName, rs.getDouble(5), maxScore, username));
            }
            context.addMessage(null, new FacesMessage("Refresh Student Exercise Progress Report", "Success"));
        } catch (SQLException ex) {
            context.addMessage(null, new FacesMessage("Refresh Student Exercise Progress Report", "Failure"));
        }
    }
    
    public void saveNewScores() {
        PreparedStatement ps = db.getPreparedStatement(UPDATE);
        FacesContext context = FacesContext.getCurrentInstance();
        
        for (ExerciseProgress e : exerciseProgressTableList){
            try {
                ps.setDouble(1, e.getScore());
                ps.setString(2, e.getExerciseName());
                ps.setString(3, e.getUsername());
                
                ps.executeUpdate();
            } catch (SQLException ex) {
                context.addMessage(null, new FacesMessage("Save New Scores", "Failure"));
            }
        }
        context.addMessage(null, new FacesMessage("Save New Scores", "Success"));
    }
    
    /*** Managed Property Setter ***/
    public void setLoginBean(InstructorLoginBean loginBean){
        this.loginBean = loginBean;
    }
}